Excel BI - Excel Challenge 867

excel-challenges

excel-formulas

🔰 Find Reps who met or exceeded target sales in at least 5 consecutive quarters and list the longest streaks.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 867

Challenge Description

🔰 Find Reps who met or exceeded target sales in at least 5 consecutive quarters and list the longest streaks. Sort on Descending of Longest Streak and Ascending on Name.

Solutions

library(tidyverse)
library(readxl)

path <- "Excel/800-899/867/867 Longest Streak.xlsx"
input <- read_excel(path, range = "A1:G101")
test <- read_excel(path, range = "I1:J7")

result <- input %>%
  arrange(Rep, Year, Quarter) %>%
  complete(
    Rep,
    Year,
    Quarter = paste0("Q", 1:4),
    fill = list(Revenue = 0, Target = 0)
  ) %>%
  mutate(goal_achieved = Revenue >= Target) %>%
  group_by(Rep) %>%
  mutate(c = cumsum(goal_achieved != lag(goal_achieved, default = FALSE))) %>%
  summarise(
    longest_streak = max(rle(goal_achieved)$lengths[
      rle(goal_achieved)$values == TRUE
    ])
  ) %>%
  ungroup() %>%
  arrange(desc(longest_streak))

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Aggregate or rank the data at the required grouping level.
Strengths: The transformation is organized around the correct grouping level, which keeps the business logic clear.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The key move is solving the problem at the right grain before shaping the final output.

import pandas as pd

path = "Excel/800-899/867/867 Longest Streak.xlsx"

input = pd.read_excel(path, usecols="A:G", nrows=100, skiprows=0)
test = pd.read_excel(path, usecols="I:J", nrows=6, skiprows=0)

df = input.sort_values("Rep")
df["goal_achieved"] = df["Revenue"] > df["Target"]
df["c"] = df.groupby("Rep")["goal_achieved"].transform(lambda x: (~x).cumsum())
streaks = df.groupby(["Rep", "c"]).size().reset_index(name="streak")
result = streaks.groupby("Rep")["streak"].max().reset_index(name="longest_streak")
result = result.sort_values("longest_streak", ascending=False).reset_index(drop=True)

print(result)

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Medium

The individual steps are manageable, but the correct transformation pattern is not obvious from the raw data.